package com.ecable.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import com.ecable.bean.VillageTO;
import com.ecable.daoI.VillageDaoI;
import com.ecable.dao.AbstractDataAccessObject;


public class VillageDaoImpl implements VillageDaoI {

	Connection con;
	PreparedStatement pstmt;
	Statement stmt;
	ResultSet rs;
	ResultSet rs1;

	public VillageDaoImpl() {

		con = new AbstractDataAccessObject().getConnection();

	}

	public boolean insertVillage(VillageTO vto) {
		boolean flag = true;

		try {

			pstmt = con
					.prepareStatement("insert into village values((select nvl(max(vid),100)+1 from village),(select stateid from state where statename=?),(select did from district where dname=?),?)");

			String vname = vto.getVname();
			System.out.println("in insert dao vname is.........." + vname);
			String sname = vto.getSname();
			System.out.println("in insert dao sname is.........." + sname);
			String dname = vto.getDname();
			System.out.println("in insert dao dname is.........." + dname);

			pstmt.setString(1, sname);
			pstmt.setString(2, dname);

			pstmt.setString(3, vname);

			int insert = pstmt.executeUpdate();

			if (insert > 0) {

				con.commit();
			} else {
				flag = false;
				con.rollback();
			}

		}

		catch (SQLException e) {
			e.printStackTrace();
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		return flag;
	}

	public VillageTO viewVillageAt(int vid1) {

		VillageTO sf = new VillageTO();
		String sname = "";
		String dname = "";

		try {

			pstmt = con.prepareStatement("select *from village where vid=?");

			pstmt.setInt(1, vid1);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				int vid = rs.getInt(1);
				int sid = rs.getInt(2);
				int did = rs.getInt(3);
				String vname = rs.getString(4);

				stmt = con.createStatement();

				rs1 = stmt
						.executeQuery("select s.statename,d.dname,v.villagename from state s,district d,village v  where v.stateid=s.stateid and v.did=d.did and v.stateid="
								+ sid + " and v.did=" + did +"and v.vid="+vid);

				if (rs1.next()) {
					sname = rs1.getString(1);
					dname = rs1.getString(2);
					vname = rs1.getString(3);
				}

				sf.setDid(did);
				sf.setDname(dname);

				sf.setSid(sid);
				sf.setSname(sname);
				sf.setVid(vid);
				sf.setVname(vname);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sf;

	}

	public Vector<VillageTO> viewVillage() {

		Vector<VillageTO> vsb = null;
		String sname = "";
		String dname = "";

		try {

			pstmt = con.prepareStatement("select *from village");

			rs = pstmt.executeQuery();

			vsb = new Vector<VillageTO>();
			while (rs.next()) {
				int vid = rs.getInt(1);
				int sid = rs.getInt(2);
				int did = rs.getInt(3);

				String vname = rs.getString(4);

				VillageTO sf = new VillageTO();

				stmt = con.createStatement();
				rs1 = stmt
						.executeQuery("select s.statename,d.dname from state s,district d,village v  where v.stateid=s.stateid and v.did=d.did and  v.stateid="
								+ sid + "and v.did=" + did);

				if (rs1.next()) {
					sname = rs1.getString(1);
					dname = rs1.getString(2);

				}

				sf.setDid(did);
				sf.setDname(dname);

				sf.setSid(sid);
				sf.setSname(sname);
				sf.setVid(vid);
				sf.setVname(vname);

				vsb.add(sf);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vsb;

	}

	public Vector<VillageTO> getVillageName(String dname) {

		Vector<VillageTO> vsb = null;

		try {

			pstmt = con
					.prepareStatement("select villagename  from village where  did in(select did from district where dname=?)");

			pstmt.setString(1, dname);

			rs = pstmt.executeQuery();
			vsb = new Vector<VillageTO>();
			int count=0;
			while (rs.next()) {

				String vname = rs.getString(1);
		

				VillageTO vf = new VillageTO();
				System.out.println(vname);
				
				vf.setVname(vname);

				vsb.add(vf);
System.out.println(++count);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vsb;

	}

	public boolean deleteVillage(int vid) {
		boolean flag = true;

		try {

			pstmt = con.prepareStatement("delete from village where vid=?");

			pstmt.setInt(1, vid);

			int delete = pstmt.executeUpdate();

			if (delete > 0) {

				con.commit();
			} else {
				flag = false;
				con.rollback();
			}

		}

		catch (SQLException e) {
			e.printStackTrace();
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		return flag;
	}

	public boolean updateVillage(VillageTO sf) {

		boolean flag = true;
		try {

			pstmt = con
					.prepareStatement("update village set villagename=? where vid=?");

			String vname = sf.getVname();
			int vid = sf.getVid();

			pstmt.setString(1, vname);
			pstmt.setInt(2, vid);

			int update = pstmt.executeUpdate();

			if (update > 0) {

				con.commit();
			} else {
				flag = false;
				con.rollback();
			}

		}

		catch (SQLException e) {
			e.printStackTrace();
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		return flag;
	}

	public Vector<VillageTO> viewVillage(String sname, String dname) {

		Vector<VillageTO> vsb = null;

		try {

			pstmt = con
					.prepareStatement("select vid,villagename from village where stateid in(select stateid from state where statename=?)and did in(select did from district where dname=?)");

			pstmt.setString(1, sname);
			pstmt.setString(2, dname);

			rs = pstmt.executeQuery();
			vsb = new Vector<VillageTO>();

			while (rs.next()) {

				int vid = rs.getInt(1);
				String vname = rs.getString(2);

				VillageTO sf = new VillageTO();

				sf.setVid(vid);
				sf.setVname(vname);

				vsb.add(sf);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return vsb;

	}

}
